﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.OleDb;

namespace Ko2.DB
{
    public class Ax : AxBase
    {

        #region Members

        private Ko2.Portal.Env _env = new Ko2.Portal.Env();

        #endregion

        public Exception GetShops(ref DataTable tbl)
        {
            string ConnStr = _env.GetAppSetting("AXAPTAConnect");
            string sql =
        @"
SELECT DISTINCT ID, NAME, Addr, Phone, ModifiedDate, ModifiedTime
FROM DB02.MAIN.dbo.v_khim_shops AS v_khim_shops_1
ORDER BY NAME 
";
            return GetTable(ref tbl, ConnStr, sql);
        }

        public Exception GetShopsRegions(ref DataTable tbl)
        {
            string ConnStr = _env.GetAppSetting("DB04Connect");
            string sql =
        @"
SELECT dbo.vw_a_shops.ID, dbo.vw_a_shops.NAME, dbo.ShopRegion.Region 
FROM dbo.vw_a_shops LEFT OUTER JOIN 
dbo.ShopRegion ON dbo.vw_a_shops.NAME = dbo.ShopRegion.Shop 
ORDER BY dbo.vw_a_shops.NAME 
";
            return GetTable(ref tbl, ConnStr, sql);
        }

        public Exception GetTradeProgress(ref DataTable tbl, DateTime dt, string ShopName)
        {
            string ConnStr = _env.GetAppSetting("AXAPTAConnect");

            string where = "";
            if (!String.IsNullOrEmpty(ShopName))
                where = "WHERE [магазин] = '" + ShopName + "' ";

            string sql =
        @"
SELECT 
магазин AS Магазин
, SUM([Сумма реализации] - [Сумма возвратов] + [Сумма оптовой реализации]) AS Сумма
FROM 
(
SELECT     CONVERT(varchar(20), CONVERT(datetime, d.TRANSDATE), 120) AS дата, i.NAME AS магазин, r.NAME AS секция, SUM(d.AMOUNTSALE) AS [Сумма реализации], 
                      SUM(d.AMOUNTPURCH) AS [Сумма проданного товара в закупочных ценах], SUM(d.RETURNAMOUNTPURCH) AS [Сумма возвратов в закупочных ценах], 
                      SUM(d.RETURNAMOUNTSALE) AS [Сумма возвратов], SUM(d.OPTAMOUNTPURCH) AS [Сумма оптовой реализации в закупочных ценах], SUM(d.OPTAMOUNTSALE) 
                      AS [Сумма оптовой реализации]
FROM         DB02.MAIN.dbo.RETAILCASHREPORTDETAILS AS d INNER JOIN
                      DB02.MAIN.dbo.INVENTLOCATION AS i ON d.INVENTLOCATIONID = i.INVENTLOCATIONID INNER JOIN
                      DB02.MAIN.dbo.INVENTITEMRANGE AS r ON d.ITEMRANGEID = r.RANGEID
GROUP BY d.TRANSDATE, i.NAME, r.NAME
) tbl 
" + where +
String.Format(@" GROUP BY дата, магазин
HAVING (дата = '{0:0000}-{1:00}-{2:00} 00:00:00')
ORDER BY Магазин ", dt.Year, dt.Month, dt.Day);

            return GetTable(ref tbl, ConnStr, sql);
        }

    }

}
